package top.zhangjianyong.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.config.YamlPropertiesFactoryBean;
import org.springframework.core.io.ClassPathResource;

import java.sql.*;
import java.util.*;

/**
 * @author ： zhangjianyong
 * @date ： 2023/3/3 2:25 PM
 */
@Slf4j
public class JDBCUtil {

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    // 1.创建druid数据源（连接池）对象
    private static final DruidDataSource dataSource = new DruidDataSource();

    public static void init() {
        YamlPropertiesFactoryBean yamlProFb = new YamlPropertiesFactoryBean();
        yamlProFb.setResources(new ClassPathResource("application.yml"));
        Properties properties = yamlProFb.getObject();
        String url = properties.getProperty("spring.datasource.druid.url");
        String username = properties.getProperty("spring.datasource.druid.username");
        String password = properties.getProperty("spring.datasource.druid.password");
        init(url, username, password);

        // 指定初始化个数
        dataSource.setInitialSize(5);
        // 指定最大个数
        dataSource.setMaxActive(100);
        // 指定低峰期个数
        dataSource.setMinIdle(20);
        // 指定等待时间
        dataSource.setMaxWait(3000);

    }

    public static void init(String url, String username, String password) {

        // 2.配置数据库基本四项，创建连接，初始化连接池容器
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);

    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();

    }

    public static void close(Connection connection) throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }

    public static void close(Statement statement) throws SQLException {
        if (statement != null && !statement.isClosed()) {
            statement.close();
        }
    }

    public static void close(ResultSet rs) throws SQLException {
        if (rs != null && !rs.isClosed()) {
            rs.close();
        }
    }

    public static void close(Connection connection, Statement statement, ResultSet resultset) throws SQLException {
        close(resultset);
        close(statement);
        close(connection);
    }

    public static Map<String, Object> qryObj(String sql, Object... obj) throws SQLException {
        try (Connection connection = getConnection()) {
            Map<String, Object> map = new HashMap<>();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object object = resultSet.getObject(columnLabel);
                    map.put(columnLabel, object);
                }
            }
            if (resultSet.next()) {
                throw new RuntimeException("查询多条记录");
            }
            return map;
        }
    }

    public static List<Map<String, Object>> qryList(String sql, Object... obj) throws SQLException {
        try (Connection connection = getConnection()) {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Map<String, Object>> list = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object object = resultSet.getObject(columnLabel);
                    map.put(columnLabel, object);
                }

                list.add(map);
            }
            return list;
        }

    }

    public static int update(String sql, Object... obj) throws SQLException {
        try (Connection connection = getConnection()) {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            int i = preparedStatement.executeUpdate();
            JDBCUtil.close(preparedStatement);
            return i;
        }

    }


    public static boolean exists(String sql, Object... obj) throws SQLException {
        try (Connection connection = getConnection()) {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        }
    }

    public static int insert(String sql, Object... obj) throws SQLException {
        try (Connection connection = getConnection()) {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            return preparedStatement.executeUpdate();
        }
    }

    public static void main(String[] args) throws SQLException {
        init();
        Map<String, Object> stringObjectMap = JDBCUtil.qryObj("select 1 from dual");
        System.out.println(JSONObject.toJSONString(stringObjectMap));
    }
}
